﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YK.DataAccess
{
    /// <summary>
    /// 通用数据库帮助类
    /// </summary>
    public static class DbHelper
    {
        #region 连接配置读取

        /// <summary>
        /// 数据库类型
        /// </summary>
        private static readonly string dataProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

        /// <summary>
        /// 反射数据库类型
        /// </summary>
        private static readonly DbProviderFactory dataFactory = DbProviderFactories.GetFactory(dataProvider);

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        #endregion

        #region 连接命令

        /// <summary>
        /// 创建连接
        /// </summary>
        /// <returns>dbconnection</returns>
        public static DbConnection CreateConnection()
        {
            DbConnection dbConn = dataFactory.CreateConnection();
            dbConn.ConnectionString = connectionString;
            return dbConn;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>command</returns>
        public static DbCommand CreateCommand(string cmdText, CommandType commandType, params DbParameter[] paramers)
        {
            DbConnection dbConn = CreateConnection();
            dbConn.Open();
            DbCommand dbCmd = dataFactory.CreateCommand();
            dbCmd.Connection = dbConn;
            dbCmd.CommandText = cmdText;
            dbCmd.CommandType = commandType;
            if (paramers != null)
                dbCmd.Parameters.AddRange(paramers);

            return dbCmd;
        }

        #endregion

        #region 数据执行方法

        /// <summary>
        /// 创建带参数的只读器
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>reader</returns>
        public static DbDataReader CreateDataReader(string cmdText, CommandType commandType, params DbParameter[] paramers)
        {
            DbDataReader reader = null;
            reader = CreateCommand(cmdText, commandType, paramers).ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        /// 创建无参数的只读器
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>reader</returns>
        public static DbDataReader CreateDataReader(string cmdText, CommandType commandType)
        {
            DbDataReader reader = null;
            reader = CreateCommand(cmdText, commandType, null).ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        /// 执行一个带参数的SQL/存储过程
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType commandType, params DbParameter[] paramers)
        {
            int retval = CreateCommand(cmdText, commandType, paramers).ExecuteNonQuery();
            return retval;
        }

        /// <summary>
        /// 执行一个不带参数的SQL/存储过程
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType commandType)
        {
            int retval = CreateCommand(cmdText, commandType, null).ExecuteNonQuery();
            return retval;
        }

        /// <summary>
        /// 执行一个带参数的SQL/存储过程有事务的
        /// </summary>
        /// <param name="tran">事务</param>
        /// <param name="cmdText">命令</param>
        /// <param name="commmandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(DbTransaction tran, string cmdText, CommandType commmandType, params DbParameter[] paramers)
        {
            DbConnection dbConn = tran.Connection;
            DbCommand dbCmd = dataFactory.CreateCommand();
            dbCmd.Connection = dbConn;
            dbCmd.CommandType = commmandType;
            dbCmd.CommandText = cmdText;
            dbCmd.Parameters.AddRange(paramers);
            dbCmd.Transaction = tran;
            int retval = dbCmd.ExecuteNonQuery();
            return retval;
        }

        /// <summary>
        /// 执行一个无参数的SQL/存储过程有事务的
        /// </summary>
        /// <param name="tran">事务</param>
        /// <param name="cmdText">命令</param>
        /// <param name="commmandType">命令类型</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(DbTransaction tran, string cmdText, CommandType commmandType)
        {
            DbConnection dbConn = tran.Connection;
            DbCommand dbCmd = dataFactory.CreateCommand();
            dbCmd.Connection = dbConn;
            dbCmd.CommandType = commmandType;
            dbCmd.CommandText = cmdText;
            dbCmd.Transaction = tran;
            int retval = dbCmd.ExecuteNonQuery();
            return retval;
        }

        /// <summary>
        /// 执行一个带参数的SQL/存储过程返回首行首列
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>值</returns>
        public static object ExecuteScalar(string cmdText, CommandType commandType, params DbParameter[] paramers)
        {
            object retval = CreateCommand(cmdText, commandType, paramers).ExecuteScalar();
            return retval;
        }

        /// <summary>
        /// 执行一个无参数的SQL/存储过程返回首行首列
        /// </summary>
        /// <param name="cmdText">命令</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paramers">参数数组</param>
        /// <returns>值</returns>
        public static object ExecuteScalar(string cmdText, CommandType commandType)
        {
            object retval = CreateCommand(cmdText, commandType, null).ExecuteScalar();
            return retval;
        }

        #endregion

        #region 创建参数方法
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="pName">参数名</param>
        /// <param name="pValue">参数值</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParamer(string pName, object pValue)
        {
            DbParameter paramer = dataFactory.CreateParameter();
            paramer.ParameterName = pName;
            paramer.Value = pValue;
            return paramer;
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="pName">参数名</param>
        /// <param name="pValue">参数值</param>
        /// <param name="pType">参数类型</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParamer(string pName, object pValue, DbType pType)
        {
            DbParameter paramer = dataFactory.CreateParameter();
            paramer.ParameterName = pName;
            paramer.Value = pValue;
            paramer.DbType = pType;
            return paramer;
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="pName">参数名</param>
        /// <param name="pValue">参数值</param>
        /// <param name="pType">参数类型</param>
        /// <param name="pSize">长度</param>
        /// <returns>参数</returns>
        public static DbParameter CreateParamer(string pName, object pValue, DbType pType, int pSize)
        {
            DbParameter paramer = dataFactory.CreateParameter();
            paramer.ParameterName = pName;
            paramer.Value = pValue;
            paramer.DbType = pType;
            paramer.Size = pSize;
            return paramer;
        }

        #endregion
    }
}
